package com.threeti.mecool.core.application.report;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StopWatch;

import com.threeti.mecool.core.domain.model.plan.PlanJobResult;
import com.threeti.mecool.core.domain.model.plan.WorkFlowConst;

/**
 * 报表缓存相关服务<p/>
 * 
 * 如有需要,可独立跑在时程服务中
 * 
 * @author Jay Meng
 */
@Service
public class ReportCacheService {
  public static final Logger logger = LoggerFactory.getLogger(ReportCacheService.class);
  
  private static final String SOR_CHECK_ADDITIONAL_SQL = "where JOB_CODE = '"+WorkFlowConst.CODE_WF_JOB_TYPE_STORE_OPENING+"'";
  private static final String STATUS_CHECK_ADDITIONAL_SQL1 = "where JOB_STATUS = '" + PlanJobResult.JOB_STATUS_NOT_FINISHED + "'";
  private static final String STATUS_CHECK_ADDITIONAL_SQL2 = "where FINISHED_AT IS NOT NULL";
  private static final String STATUS_CHECK_ADDITIONAL_SQL3 = "where NOTES IS NULL";

  @Autowired
  private JdbcTemplate jdbcTemplate;
  private int cacheCheckInterval;
  
  //@PostConstruct//服务启动时，强制刷新一次
  public void forceRefreshReportCache() {
    doRefresh("Profiling for cache refresh of 门店开店率-各省市摘要汇总报表(对全国所有省市进行摘要汇总)...", "DM_MV_SOR_SUM_BY_PROVINCE_ALL");
    doRefresh("Profiling for cache refresh of (督导)(巡店共用)任务执行率-各省市摘要汇总报表(对全国所有省市进行摘要汇总)...", "DM_MV_TPR_SUM_BY_PROVINCE_ALL");  	
  }

  /**
   * 定时检查实时报表的缓存，如有需要，那么强制对相关缓存进行强制更新<p/>
   * 
   * 1.目前暂不依赖DB端自身的定时任务或来自于Master表的主动commit更新(依赖Master表的特殊日志)等机制<br/>
   * 2.每次运行，需要先检查是否有必要对缓存进行更新，该检查策略可能有所变化<br/>
   * 3.短时间内,可能存在大于1次的缓存强制刷新 TODO jay:optimized
   */
  public void checkOrRefreshReportCache() {
    StopWatch clockAround = new StopWatch("Profiling for all the realtime report cache refresh...");
    clockAround.start();
    
    if (isNeedRefreshWithFitler(SOR_CHECK_ADDITIONAL_SQL)) {
      doRefresh("Profiling for cache refresh of 门店开店率-各省市摘要汇总报表(对全国所有省市进行摘要汇总)...", "DM_MV_SOR_SUM_BY_PROVINCE_ALL");
  
      //doRefresh("Profiling for cache refresh of 门店开店率-摘要汇总报表(按特定省市进行摘要汇总)...", "DM_MV_SOR_SUM_BY_PROVINCE");
  
      //doRefresh("Profiling for cache refresh of 门店开店率-摘要汇总报表(按特定城市进行摘要汇总)...", "DM_MV_SOR_SUM_BY_CITY");
    }

    if (isNeedForceRefresh()) {
      doRefresh("Profiling for cache refresh of (督导)(巡店共用)任务执行率-各省市摘要汇总报表(对全国所有省市进行摘要汇总)...", "DM_MV_TPR_SUM_BY_PROVINCE_ALL");
      
      //doRefresh("Profiling for cache refresh of (督导)(巡店共用)任务执行率-摘要汇总报表(按特定省市进行摘要汇总)...", "DM_MV_TPR_SUM_BY_PROVINCE");
      
      //doRefresh("Profiling for cache refresh of (督导)任务执行率-摘要汇总报表(按特定城市进行摘要汇总)...", "DM_MV_TPR_SUM_BY_CITY");
      
      //doRefresh("Profiling for cache refresh of (督导)巡店任务执行率-摘要汇总报表(按督导和特定城市进行摘要汇总)...", "DM_MV_STPR_BY_CITY");
    }
    
    clockAround.stop();
    logger.debug(clockAround.prettyPrint());
  }
  
  public void doRefresh(String taskProfileIdOrDesc, String cacheId) {
    StopWatch clock = new StopWatch(taskProfileIdOrDesc);
    clock.start();
    jdbcTemplate.execute("call dbms_mview.refresh('"+cacheId+"')");
    clock.stop();
    logger.debug(clock.prettyPrint());
  }
  
  public boolean isNeedRefreshWithSorCheck() {
  	return isNeedRefreshWithFitler(SOR_CHECK_ADDITIONAL_SQL);
  }
  
  private boolean isNeedRefreshWithFitler(String additionalFilterSql) {
    if (additionalFilterSql == null) additionalFilterSql = "";
    int intervalRecent = 5;//cacheCheckInterval / 2
    int intervalRecentBefore = cacheCheckInterval * 2;
    
    boolean isSOR = StringUtils.isNotBlank(additionalFilterSql);//临时判断，目前只有开店率和非开店率两种类型的报表
    
    long countRecent = jdbcTemplate.queryForLong("select count(*) from DM_WF_PLAN_JOB_RESULT as of timestamp (systimestamp - interval '"+intervalRecent+"'second) " + additionalFilterSql);
    long countRecentBefore = jdbcTemplate.queryForLong("select count(*) from DM_WF_PLAN_JOB_RESULT as of timestamp (systimestamp - interval '"+intervalRecentBefore+"'second) " + additionalFilterSql);

    String logPrefix = "";
    if (isSOR) {
      logPrefix = "[StoreOpening]";
    }
    
    logger.debug(logPrefix + "countRecent of master table(DM_WF_PLAN_JOB_RESULT): " + countRecent);
    logger.debug(logPrefix + "countRecentBefore of master table(DM_WF_PLAN_JOB_RESULT): " + countRecentBefore);
    
    if (countRecent != countRecentBefore) {
      return true;
    }
    
    String statusSql1 = STATUS_CHECK_ADDITIONAL_SQL1;
    String statusSql2 = STATUS_CHECK_ADDITIONAL_SQL2;
    String statusSql3 = STATUS_CHECK_ADDITIONAL_SQL3;
    if (isSOR) {
      statusSql1 = statusSql1.replaceAll("where", " and ");
      statusSql2 = statusSql2.replaceAll("where", " and ");
      statusSql3 = statusSql3.replaceAll("where", " and ");
    }
    
    long countOfStatus1Recent = jdbcTemplate.queryForLong("select count(*) from DM_WF_PLAN_JOB_RESULT as of timestamp (systimestamp - interval '"+intervalRecent+"'second) " + additionalFilterSql + statusSql1);
    long countOfStatus1RecentBefore = jdbcTemplate.queryForLong("select count(*) from DM_WF_PLAN_JOB_RESULT as of timestamp (systimestamp - interval '"+intervalRecentBefore+"'second) " + additionalFilterSql + statusSql1);
    logger.debug(logPrefix + "countOfStatus(JOB_STATUS)Recent of master table(DM_WF_PLAN_JOB_RESULT): " + countOfStatus1Recent);
    logger.debug(logPrefix + "countOfStatus(JOB_STATUS)RecentBefore of master table(DM_WF_PLAN_JOB_RESULT): " + countOfStatus1RecentBefore);
    if (countOfStatus1Recent != countOfStatus1RecentBefore) {
      return true;
    }    
    long countOfStatus2Recent = jdbcTemplate.queryForLong("select count(*) from DM_WF_PLAN_JOB_RESULT as of timestamp (systimestamp - interval '"+intervalRecent+"'second) " + additionalFilterSql + statusSql2);
    long countOfStatus2RecentBefore = jdbcTemplate.queryForLong("select count(*) from DM_WF_PLAN_JOB_RESULT as of timestamp (systimestamp - interval '"+intervalRecentBefore+"'second) " + additionalFilterSql + statusSql2);
    logger.debug(logPrefix + "countOfStatus(FINISHED_AT)Recent of master table(DM_WF_PLAN_JOB_RESULT): " + countOfStatus2Recent);
    logger.debug(logPrefix + "countOfStatus(FINISHED_AT)RecentBefore of master table(DM_WF_PLAN_JOB_RESULT): " + countOfStatus2RecentBefore);
    if (countOfStatus2Recent != countOfStatus2RecentBefore) {
      return true;
    }
    long countOfStatus3Recent = jdbcTemplate.queryForLong("select count(*) from DM_WF_PLAN_JOB_RESULT as of timestamp (systimestamp - interval '"+intervalRecent+"'second) " + additionalFilterSql + statusSql3);
    long countOfStatus3RecentBefore = jdbcTemplate.queryForLong("select count(*) from DM_WF_PLAN_JOB_RESULT as of timestamp (systimestamp - interval '"+intervalRecentBefore+"'second) " + additionalFilterSql + statusSql3);
    logger.debug(logPrefix + "countOfStatus(NOTES)Recent of master table(DM_WF_PLAN_JOB_RESULT): " + countOfStatus3Recent);
    logger.debug(logPrefix + "countOfStatus(NOTES)RecentBefore of master table(DM_WF_PLAN_JOB_RESULT): " + countOfStatus3RecentBefore);
    if (countOfStatus3Recent != countOfStatus3RecentBefore) {
      return true;
    }
    return false;
  }
  
  public boolean isNeedForceRefresh() {
    return isNeedRefreshWithFitler("");
  }

  @Value("${report.cache.checkinterval}")
  public void setCacheCheckInterval(int cacheCheckInterval) {
    this.cacheCheckInterval = cacheCheckInterval / 1000;//cacheCheckInterval using second
  }
}
